import datetime
import math
import os
import sys
import time
from datetime import datetime, timedelta

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.db.AbsSqlServer import AbsSqlServerDev
from src.utils import CommonUtils
from src.db.CcxDataCenterDb import CcxDataCenter
from src.db.ProdDb import ProdDb
from src.utils.LogUtils import Logger
from src.db.FCDB import FCDB

logger = Logger()


# 计算主体利差
class EnterpriseInterestRateSpread:
    def __init__(self, logger, localDb, dataCenterDb, absSqlServer):
        self.logger = logger
        self.localDb = localDb
        self.dataCenterDb = dataCenterDb
        self.arg = sys.argv
        self.fcdb = FCDB(logSql=False, autocommit=True)
        self.absSqlServer = absSqlServer
        # self.cursor = AbsSqlServer.Conn.conn.cursor()

    def __queryAllTradeList__(self, startDate):
        # 获取当前时间
        current_time = datetime.now()
        endDate = current_time.strftime("%Y%m%d")

        if startDate is None:
            startDate = '20200601'
        sql = """
            select
    
                TRADEDATE
                from
                fcdb.tq_oa_trdschedule
                where EXCHANGE=001002
                and ISVALID=1
                and TRADEDATE >= %s
                and TRADEDATE <%s
            order by TRADEDATE
        """
        return self.fcdb.Query(sql, (startDate, endDate))

    def calByDb(self, tradeDate, entType):
        tableName = None
        if entType == 'T002':
            tableName = 'base_bonds_interest_rate_spread_platform'
        if entType == 'T001':
            tableName = 'base_bonds_interest_rate_spread_industry'
        sql = """
                   with
                        base_data as (
                             select comp_code,credit_code,actissamt,interest_rate_spread,raise_mode from """ + tableName + """ where trade_date=%s	
                        ),
                        base_weight_valye as (
                             select
                                    max(comp_code) as 'comp_code',
                                    credit_code,
                                    sum(actissamt) as 'totalActissamt',
                                    sum(interest_rate_spread*actissamt) as 'weightingValue'
                                    from
                                    base_data
                            
                                    group by credit_code
                        ),
                        base_median as (
                             SELECT
                                    DISTINCT 
                                    credit_code,
                                    PERCENTILE_CONT(0.5) WITHIN GROUP (
                                    ORDER BY interest_rate_spread) OVER (PARTITION by credit_code) AS
                                        p75_sales_amount
                                    FROM
                                       base_data
                        ),
                        publicOffering as (
                             SELECT
                                    DISTINCT 
                                    credit_code,
                                    PERCENTILE_CONT(0.5) WITHIN GROUP (
                                    ORDER BY interest_rate_spread) OVER (PARTITION by credit_code) AS
                                        p75_sales_amount
                                    FROM
                                      base_data where raise_mode =1
                        ),
                        publicOfferingWeightingValue as (
                             select
                                    credit_code,
                                    sum(actissamt) as 'totalActissamt',
                                    sum(interest_rate_spread*actissamt) as 'weightingValue'
                                    from
                                    base_data	 where raise_mode =1
                            
                                    group by credit_code
                        ),
                        perpetualBonds as (
                            SELECT
                                    DISTINCT 
                                    credit_code,
                                    PERCENTILE_CONT(0.5) WITHIN GROUP (
                                    ORDER BY interest_rate_spread) OVER (PARTITION by credit_code) AS
                                        p75_sales_amount
                                    FROM
                                       base_data where raise_mode =3
                        
                        ),
                        perpetualBondsWeightingValue as (
                            SELECT
                                    credit_code,
                                    sum(actissamt) as 'totalActissamt',
                                    sum(interest_rate_spread*actissamt) as 'weightingValue'
                                    from
                                    base_data	where raise_mode =3
                                    group by credit_code
                        
                        ),
                        -- 私募
                        privatePlacement as (
                             SELECT
                                    DISTINCT 
                                    credit_code,
                                    PERCENTILE_CONT(0.5) WITHIN GROUP (
                                    ORDER BY interest_rate_spread) OVER (PARTITION by credit_code) AS
                                        p75_sales_amount
                                    FROM
                                       base_data where raise_mode =2
                        ),
                        privatePlacementWeightingValue as (
                             select
                                    credit_code,
                                    sum(actissamt) as 'totalActissamt',
                                    sum(interest_rate_spread*actissamt) as 'weightingValue'
                                    from
                                    base_data	 where raise_mode =2
                            
                                    group by credit_code
                        ),
                        -- 公募+私募
                        privateAndPublic as (
                             SELECT
                                    DISTINCT 
                                    credit_code,
                                    PERCENTILE_CONT(0.5) WITHIN GROUP (
                                    ORDER BY interest_rate_spread) OVER (PARTITION by credit_code) AS
                                        p75_sales_amount
                                    FROM
                                       base_data where raise_mode =2 or raise_mode =1
                        ),
                        privateAndPublicWeightingValue as (
                             select
                                    credit_code,
                                    sum(actissamt) as 'totalActissamt',
                                    sum(interest_rate_spread*actissamt) as 'weightingValue'
                                    from
                                    base_data	 where raise_mode =2 or raise_mode =1
                            
                                    group by credit_code
                        )
                        select
                        	a.comp_code,
                            a.credit_code,
                            a.totalActissamt,
                            a.weightingValue,
                            b.p75_sales_amount as '全部债券中位数',
                            c.p75_sales_amount as '公募中位数',
                            d.weightingValue as '公募加权平均数',
                            d.totalActissamt as '公募权重总和',
                            e.p75_sales_amount as '永续债中位数',
                            f.weightingValue as '永续债利差总额',
                            f.totalActissamt as '永续债权重总和',
                            g.p75_sales_amount as '私募中位数',
                            h.weightingValue as '私募加权平均数',
                            h.totalActissamt as '私募权重总和',
                            i.p75_sales_amount as '公募+私募中位数',
                            j.weightingValue as '公募+私募加权平均数',
                            j.totalActissamt as '公募+私募权重总和'
                        from base_weight_valye a
                        left join base_median b on a.credit_code=b.credit_code
                        left join publicOffering c on c.credit_code=a.credit_code
                        left join publicOfferingWeightingValue d on d.credit_code=a.credit_code
                        left join perpetualBonds e on e.credit_code=a.credit_code
                        left join perpetualBondsWeightingValue f on f.credit_code=a.credit_code
                        left join privatePlacement g on g.credit_code=a.credit_code
                        left join privatePlacementWeightingValue h on h.credit_code=a.credit_code
                        left join privateAndPublic i on i.credit_code=a.credit_code
                        left join privateAndPublicWeightingValue j on j.credit_code=a.credit_code
        """
        return self.absSqlServer.execute_query(sql, tradeDate)

    def __queryEntList__(self):
        sql = """
                   SELECT
                    id,
                    enterprise_name,
                    credit_code,
                    ent_type,
                    debt_num,
                    debt_scale,
                    comp_code
                FROM
                    regular_income_v2.ent_enterprise;
        """
        # return self.localDb.ExecMany(sql, updateDatas)
        many = self.localDb.Query(sql)
        return many

    # 根据信用代码抽取债券利差数据
    def __queryBondsRateSpreadByEnt__(self, creditCode, compCode):
        sql = """
                 select
                    credit_code,comp_code ,raise_mode,trade_date,interest_rate_spread 
                from
                base_bonds_interest_rate_spread
                where credit_code  =%s 
                
        """
        result = self.absSqlServer.execute_query(sql, creditCode)
        return result

    # cvtbdexpirememp包含N的为永续债的加权平均数
    def calPerpetualBonds(self):
        sql = """
            with b as (
                select
                credit_code,
                sum(actissamt) as 'totalActissamt',
                sum(interest_rate_spread*actissamt) as 'weightingValue'
                from
                base_bonds_interest_rate_spread
                where trade_date='20240202'	
                AND cvtbdexpirememp like '%N%'
                group by credit_code
                )
                SELECT * from b 
        """

    # 指定日期计算加权平均数
    def calWeightingValueByDate(self, tradeDate):
        sql = """
                with b as (
                    select
                    credit_code,
                    sum(actissamt) as 'totalActissamt',
                    sum(interest_rate_spread*actissamt) as 'weightingValue'
                    from
                    base_bonds_interest_rate_spread
                    where trade_date='20240202'	
            
                    group by credit_code
                    )
                    SELECT * from b 
        """
        return self.absSqlServer.execute_query(sql, (tradeDate))

    # 指定日期计算中位数
    def calMedianByDate(self, tradeDate):
        sql = """
                with a as(
                    SELECT
                        secode,
                        credit_code,
                        PERCENTILE_CONT(0.5) WITHIN GROUP (
                        ORDER BY interest_rate_spread) OVER (PARTITION by credit_code) AS
                            p75_sales_amount
                        FROM
                            base_bonds_interest_rate_spread
                                where trade_date ='20240202'
                        )
                select
                    credit_code,
                    max(p75_sales_amount)as 'median'
                from
                    a
                        group by a.credit_code
        """
        return self.absSqlServer.execute_query(sql, (tradeDate))

    def deleteDataByDate(self, date):
        sql = """
         DELETE FROM Fits2Test.dbo.base_bonds_interest_rate_spread_enterprise WHERE  trade_date = %s
        """
        many = self.absSqlServer.execute_non_query(sql, date)
        return many

    def queryNewAllTradeList(self):
        sql = """
            select distinct  TRADEDATE  from tq_qt_yieldcurve tqy 
            order by TRADEDATE 
        """
        query = self.fcdb.Query(sql)
        tradeList = [item['TRADEDATE'] for item in query]
        return tradeList

        # 根据交易日序列 计算前一天bp差值

    def daily_difference_bp(self, startDate, endDate):
        sql = """
            WITH CTE AS (
                SELECT 
                    trade_date, 
                    all_bp_weight_value as 'value_field'
                    ,LAG(all_bp_weight_value, 1, all_bp_weight_value) OVER (PARTITION BY credit_code ORDER BY trade_date) AS prev_value_field,
                    credit_code,
                    comp_code
                FROM Fits2Test.dbo.base_bonds_interest_rate_spread_enterprise
                WHERE trade_date = %s or trade_date=%s
                
            )
            SELECT 
                credit_code, 
                trade_date,
                value_field - prev_value_field AS difference,
                comp_code,
                value_field as value1,
                prev_value_field as value2
            FROM CTE
            WHERE trade_date=%s
            ORDER BY credit_code

        """
        return self.absSqlServer.execute_query(sql, (startDate, endDate, endDate))

    def calWarningBasicData(self):
        # 开始时间 startDate
        # 获取当前时间
        current_time = datetime.now()
        # 昨天
        yesterday_time = current_time - timedelta(days=1)
        # 前天
        前天 = (current_time - timedelta(days=2)).strftime("%Y%m%d")
        # 格式化为字符串（例如：年-月-日 时:分:秒）
        yesterdayDate = yesterday_time.strftime("%Y%m%d")
        currentDate = current_time.strftime("%Y%m%d")
        tradeList = self.queryNewAllTradeList()

        startDate = None
        endDate = None
        # 因跑批时间是在当日凌晨到早上
        # 判断前一天 和大前天是否是交易日
        if tradeList.__contains__(yesterdayDate) and tradeList.__contains__(前天):
            startDate = 前天
            endDate = yesterdayDate
        # 昨天不是交易日
        elif tradeList.__contains__(yesterdayDate) == False:
            # 往前推到合法交易日
            tradingDay = list(filter(lambda x: x < yesterdayDate, tradeList))
            tradeDateMax = max(tradingDay)
            if int(yesterdayDate) - int(tradeDateMax) >= 1:
                logger.info("离最近得交易日大于等于1天，跳过")
                exit()
            else:
                startDate = tradeDateMax
                endDate = int(tradeDateMax) - 1
        # 昨天是交易日
        elif tradeList.__contains__(yesterdayDate):
            startDate = max(list(filter(lambda x: x < yesterdayDate, tradeList)))
            endDate = yesterdayDate
        else:
            logger.info("未获取到合法交易日，跳过")
            exit()
        if startDate is not None and endDate is not None:
            # 抽取两年的数据
            dailyDifferenceBpData = self.daily_difference_bp(startDate, endDate)
            self.savedailyDifferenceBpData(dailyDifferenceBpData, endDate, currentDate)
            pass
        pass

    def savedailyDifferenceBpData(self, listData, endDate, currentDate):
        sql = """
            INSERT INTO Fits2Test.dbo.base_bonds_interest_rate_spread_enterprise_data
             (credit_code, comp_code, trade_date, daily_difference_bp,batch_date,current_value,last_value)
             VALUES(%s, %s, %s, %s,%s,%s,%s);
        """
        convertData = [(item['credit_code'], item['comp_code'], item['trade_date'], item['difference'], currentDate,
                        item['value1'], item['value2']) for
                       item in
                       listData]

        deleteSql = """
            DELETE FROM Fits2Test.dbo.base_bonds_interest_rate_spread_enterprise_data WHERE 
            batch_date=%s
        """
        self.absSqlServer.execute_non_query(deleteSql, currentDate)
        many = self.absSqlServer.execute_many(sql, convertData)
        return many

    # 计算主体利差
    def process(self):
        dateByCommand = CommonUtils.isCommandParam(self.arg, "startDate")
        if dateByCommand['exist']:
            startDate = dateByCommand["value"]
        else:
            # 获取当前时间
            current_time = datetime.now()
            # 减去一天
            yesterday_time = current_time - timedelta(days=1)
            # 格式化为字符串（例如：年-月-日 时:分:秒）
            startDate = yesterday_time.strftime("%Y%m%d")
        __tradeDate__ = self.__queryAllTradeList__(startDate)
        for tradeDate in __tradeDate__:
            tradeDate = tradeDate['TRADEDATE']
            logger.info("==========计算交易日：" + tradeDate + "主体利差")
            # 计算城投主体利差
            result = []
            logger.info("拉取交易日：" + tradeDate + "城投债券利差数据")
            t1 = time.perf_counter()
            T002_Data = self.calByDb(tradeDate, "T002")
            CommonUtils.statisticsTime(t1, "拉取交易日：%s城投债券利差数据" % (tradeDate))
            # 计算产业主体利差
            t2 = time.perf_counter()
            logger.info("拉取交易日：" + tradeDate + "产业债券利差数据")
            T001_Data = self.calByDb(tradeDate, "T001")
            CommonUtils.statisticsTime(t2, ("拉取交易日：" + tradeDate + "产业债券利差数据"))

            t3 = time.perf_counter()
            data = self.convertData(T002_Data + T001_Data, tradeDate)
            self.deleteDataByDate(tradeDate)
            CommonUtils.statisticsTime(t3, ("删除旧数据并保存"))
            self.__updateLocal__(data)
            logger.info("==========交易日：" + tradeDate + "主体债券利差数据计算完成")
        self.calWarningBasicData()

    @staticmethod
    def convertData(result: list, date):
        postData = []
        for item in result:
            postData.append((
                datetime.now(),
                item.get('comp_code'),
                date,
                item.get('credit_code'),
                item.get('全部债券中位数'),
                EnterpriseInterestRateSpread.divided(item.get('weightingValue'), item.get('totalActissamt')),

                item.get('公募+私募中位数'),
                EnterpriseInterestRateSpread.divided(item.get('公募+私募加权平均数'), item.get('公募+私募权重总和')),

                item.get('公募中位数'),
                EnterpriseInterestRateSpread.divided(item.get('公募加权平均数'), item.get('公募权重总和')),

                item.get('私募中位数'),

                EnterpriseInterestRateSpread.divided(item.get('私募加权平均数'), item.get('私募权重总和')),

                item.get('永续债中位数'),
                EnterpriseInterestRateSpread.divided(item.get('永续债利差总额'), item.get('永续债权重总和')),

            ))
        return postData

    @staticmethod
    def divided(value1, value2):
        if value1 is None or value2 is None:
            return None
        try:
            return value1 / value2
        except  Exception as e:
            return None

    def __updateLocal__(self, updateDatas):
        sql = """
            INSERT INTO Fits2Test.dbo.base_bonds_interest_rate_spread_enterprise 
            (create_date, comp_code, trade_date, credit_code, all_bp_median, all_bp_weight_value,
             private_public, private_public_weight_value, public_offering, public_offering_weight_value,
              private_placement, private_placement_weight_value, perpetual_bonds, perpetual_bonds_weight_value)
             VALUES(%s ,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
        """
        # return self.localDb.ExecMany(sql, updateDatas)
        many = self.absSqlServer.execute_many(sql, updateDatas)
        return many


# python EnterpriseInterestRateSpread.py --date=20200101 --endDate=20201231
if __name__ == "__main__":
    logger = Logger()
    localDb = ProdDb(logSql=False, autocommit=True)
    dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
    absSqlServer = AbsSqlServerDev()
    t = time.perf_counter()
    logger.info("========计算主体利差数据 开始 ========")
    task = EnterpriseInterestRateSpread(logger=logger, localDb=localDb, dataCenterDb=dataCenterDb,
                                        absSqlServer=absSqlServer)
    task.process()

    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('总耗时: %d分%.3f秒' % (m, s))
    logger.info("========计算主体利差数据 结束 ========")
